package com.iris.live.services.data.repositories.impl;

import com.google.common.base.Strings;
import com.google.common.collect.Maps;
import com.iris.live.services.common.DateFormatUtils;
import com.iris.live.services.common.StringUtils;
import com.iris.live.services.data.models.Lsh车型Model;
import com.iris.live.services.data.models.临时接待记录Model;
import com.iris.live.services.data.models.报价单表Model;
import com.iris.live.services.data.models.顾客试驾记录Model;
import com.iris.live.services.data.repositories.*;
import com.iris.live.services.models.PageData;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by lusong on 2016/12/10.
 */
@Repository
public class ReportRepositoryImpl extends BaseRepository implements ReportRepository {

    @Autowired
    private 顾客试驾记录Repository repository顾客试驾记录;
    @Autowired
    private Lsh车型Repository lsh车型Repository;

    @Autowired
    private 临时接待记录Repository teampRecepRepository;

    @Autowired
    private 报价单表Repository 报价单表repository;


    private Logger logger = LoggerFactory.getLogger(ReportRepositoryImpl.class);

    @Override
    public List getNextAboutLsh() {
        String sql = "select a.`大区` result from lsh经销商表 a GROUP BY a.`大区` " +
                " union ALL  " +
                " select a.`小区` result from lsh经销商表 a where a.`大区` = '' group by a.`小区` " +
                " union ALL  " +
                " select a.CTA result from lsh经销商表 a where a.`大区` = '' and a.`小区` = '' group by a.CTA " +
                " union ALL  " +
                " select a.`经销商名` result from lsh经销商表 a where a.`大区` = '' and a.`小区` = '' and a.CTA = ''  group by a.`经销商名` ";
        return this.getList(sql);
    }

    @Override
    public List getNextAboutDistrict(String district) {
        String sql = " select a.`小区` result from lsh经销商表 a where a.`大区` =:district and a.`小区` <> '' group by a.`小区` " +
                " union ALL  " +
                " select a.CTA result from lsh经销商表 a where a.`大区` =:district and a.`小区` = '' and a.CTA <> '' group by a.CTA " +
                " union ALL  " +
                " select a.`经销商名` result from lsh经销商表 a where a.`大区` =:district and a.`小区` = '' and a.CTA = ''  group by a.`经销商名` ";
        Map map = new HashMap();
        map.put("district", district);
        return this.getList(sql, map);
    }

    @Override
    public List getNextAboutVillage(String village) {
        String sql = " select a.CTA result from lsh经销商表 a where a.`小区` =:village and a.CTA <> '' group by a.CTA " +
                " union ALL  " +
                " select a.`经销商名` result from lsh经销商表 a where a.`小区` =:village and a.CTA = ''  group by a.`经销商名` ";
        Map map = new HashMap();
        map.put("village", village);
        return this.getList(sql, map);
    }

    @Override
    public List getNextAboutCTA(String cta) {
        String sql = " select a.`经销商名` result from lsh经销商表 a where a.CTA =:cta  group by a.`经销商名` ";
        Map map = new HashMap();
        map.put("cta", cta);
        return this.getList(sql, map);
    }

    public List getSaleManagementReportByPermitAndYear(String type, String year) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select a.MODEL,a.STAT_MONTH, ");
        //sb.append(" 	SUM(a.`STAT_YEAR`) `STAT_YEAR`, ");
        //sb.append(" 	SUM(a.`STAT_MONTH`) `STAT_MONTH`, ");
        sb.append(" 	SUM(a.`SIGNED_WHOLESALE_TA`) `SIGNED_WHOLESALE_TA`, ");
        sb.append(" 	SUM(a.`ACTUAL_WHOLESALE`) `ACTUAL_WHOLESALE`, ");
        sb.append(" 	SUM(a.`WS_ACHIEVEMENT`) `WS_ACHIEVEMENT`, ");
        sb.append(" 	SUM(a.`SIGNED_RETAIL_TA`) `SIGNED_RETAIL_TA`, ");
        sb.append(" 	SUM(a.`ACTUAL_RETAIL_NBI`) `ACTUAL_RETAIL_NBI`, ");
        sb.append(" 	SUM(a.`NBI_RT_ACHIEVEMENT`) `NBI_RT_ACHIEVEMENT`, ");
        sb.append(" 	SUM(a.`ROS_RETAIL_TA`) `ROS_RETAIL_TA`, ");
        sb.append(" 	SUM(a.`ACTUAL_RETAIL_ROS`) `ACTUAL_RETAIL_ROS`, ");
        sb.append(" 	SUM(a.`ROS_RT_ACHIEVEMENT`) `ROS_RT_ACHIEVEMENT`, ");
        sb.append(" 	SUM(a.`REMAINING_PRE_REPORT`) `REMAINING_PRE_REPORT`, ");
        sb.append(" 	SUM(a.`SALES_GUIDELINE`) `SALES_GUIDELINE`, ");
        sb.append(" 	SUM(a.`LT_89_DAYS_STOCK`) `LT_89_DAYS_STOCK`, ");
        sb.append(" 	SUM(a.`90_149_DAYS_STOCK`) `90_149_DAYS_STOCK`, ");
        sb.append(" 	SUM(a.`GET_150_DAYS_STOCK`) `GET_150_DAYS_STOCK`, ");
        sb.append(" 	SUM(a.`CLOSING_STOCK`) `CLOSING_STOCK`, ");
        sb.append(" 	SUM(a.`STOCK_LEVEL`) `STOCK_LEVEL`, ");
        sb.append(" 	SUM(a.`AGING_STOCK_GT_90_DAYS`) `AGING_STOCK_GT_90_DAYS`, ");
        sb.append(" 	SUM(a.`AGING_STOCK_GT_90_DAYS_PER`) `AGING_STOCK_GT_90_DAYS_PER`, ");
        sb.append(" 	SUM(a.`NEW_CAR_EXTERNAL_SALES_REVENUE`) `NEW_CAR_EXTERNAL_SALES_REVENUE`, ");
        sb.append(" 	SUM(a.`ACTUAL_GPI`) `ACTUAL_GPI`, ");
        sb.append(" 	SUM(a.`ACTUAL_GPII`) `ACTUAL_GPII`, ");
        sb.append(" 	SUM(a.`ACTUAL_SI`) `ACTUAL_SI`, ");
        sb.append(" 	SUM(a.`ACTUAL_GPIII`) `ACTUAL_GPIII`, ");
        sb.append(" 	SUM(a.`GPI_PER`) `GPI_PER`, ");
        sb.append(" 	SUM(a.`GPII_PER`) `GPII_PER`, ");
        sb.append(" 	SUM(a.`SI_PER`) `SI_PER`, ");
        sb.append(" 	SUM(a.`GPIII_PER`) `GPIII_PER`, ");
        sb.append(" 	SUM(a.`BUDGET_SALES_DIRECT_EXPENSES`) `BUDGET_SALES_DIRECT_EXPENSES`, ");
        sb.append(" 	SUM(a.`ACTUAL_SALES_DIRECT_EXPENSES`) `ACTUAL_SALES_DIRECT_EXPENSES`, ");
        sb.append(" 	SUM(a.`ACTUAL_DIRECT_EXPENSES_PER_CAR`) `ACTUAL_DIRECT_EXPENSES_PER_CAR`, ");
        sb.append(" 	SUM(a.`BUDGET_SALES_INDIRECT_EXPENSES`) `BUDGET_SALES_INDIRECT_EXPENSES`, ");
        sb.append(" 	SUM(a.`ACTUAL_SALES_INDIRECT_EXPENSES`) `ACTUAL_SALES_INDIRECT_EXPENSES`, ");
        sb.append(" 	SUM(a.`ACTUAL_INDIRECT_EXPENSES_PER_CAR`) `ACTUAL_INDIRECT_EXPENSES_PER_CAR`, ");
        sb.append(" 	SUM(a.`ACTUAL_TOTAL_EXPENSES_PER_CAR`) `ACTUAL_TOTAL_EXPENSES_PER_CAR`, ");
        sb.append(" 	SUM(a.`USED_CAR_APPRAISAL_VOLUME`) `USED_CAR_APPRAISAL_VOLUME`, ");
        sb.append(" 	SUM(a.`APPRAISAL`) `APPRAISAL`, ");
        sb.append(" 	SUM(a.`USED_CAR_TRADE_IN_VOLUME`) `USED_CAR_TRADE_IN_VOLUME`, ");
        sb.append(" 	SUM(a.`TRADE_IN_PER`) `TRADE_IN_PER`, ");
        sb.append(" 	SUM(a.`TRADE_IN_COMMISSION`) `TRADE_IN_COMMISSION`, ");
        sb.append(" 	SUM(a.`TRADE_IN_COMMISSION_PER_CAR`) `TRADE_IN_COMMISSION_PER_CAR`, ");
        sb.append(" 	SUM(a.`AFTER_MARKET_PRODUCT_REVENUE`) `AFTER_MARKET_PRODUCT_REVENUE`, ");
        sb.append(" 	SUM(a.`AFTER_MARKET_PRODUCT_REVENUE_PER_CAR`) `AFTER_MARKET_PRODUCT_REVENUE_PER_CAR`, ");
        sb.append(" 	SUM(a.`AFTER_MARKET_PRODUCT_GP`) `AFTER_MARKET_PRODUCT_GP`, ");
        sb.append(" 	SUM(a.`AFTER_MARKET_PRODUCT_GP_PER`) `AFTER_MARKET_PRODUCT_GP_PER`, ");
        sb.append(" 	SUM(a.`NEW_CAR_INSURANCE`) `NEW_CAR_INSURANCE`, ");
        sb.append(" 	SUM(a.`NEW_CAR_INSURANCE_RATE`) `NEW_CAR_INSURANCE_RATE`, ");
        sb.append(" 	SUM(a.`NEW_CAR_INSURANCE_COMMISSION`) `NEW_CAR_INSURANCE_COMMISSION`, ");
        sb.append(" 	SUM(a.`FIRST_YEAR_COMMERCIAL_INSURANCE_PREMIUM`) `FIRST_YEAR_COMMERCIAL_INSURANCE_PREMIUM`, ");
        sb.append(" 	SUM(a.`FIRST_YEAR_COMPULSORY_INSURANCE_PREMIUM`) `FIRST_YEAR_COMPULSORY_INSURANCE_PREMIUM`, ");
        sb.append(" 	SUM(a.`COMPULSORY_INSURANCE_PREMIUM`) `COMPULSORY_INSURANCE_PREMIUM`, ");
        sb.append(" 	SUM(a.`COMMERCIAL_INSURANCE_PREMIUM`) `COMMERCIAL_INSURANCE_PREMIUM`, ");

        sb.append(" 	SUM(a.`NEW_CAR_FINANCING`) `NEW_CAR_FINANCING`, ");
        sb.append(" 	SUM(a.`FINANCING_RATE`) `FINANCING_RATE`, ");
        sb.append(" 	SUM(a.`NEW_CAR_AGILITY`) `NEW_CAR_AGILITY`, ");
        sb.append(" 	SUM(a.`AGILITY_RATE`) `AGILITY_RATE`, ");
        sb.append(" 	SUM(a.`FINANCING_COMMISSION`) `FINANCING_COMMISSION`, ");
        sb.append(" 	SUM(a.`FINANCING_AMOUNT`) `FINANCING_AMOUNT`, ");
        sb.append(" 	SUM(a.`FINANCING_SERVICE_FEE_MARGIN`) `FINANCING_SERVICE_FEE_MARGIN`, ");
        sb.append(" 	SUM(a.`FINANCING_SERVICE_FEE_MARGIN_PER_CAR`) `FINANCING_SERVICE_FEE_MARGIN_PER_CAR`, ");
        sb.append(" 	SUM(a.`REGISTRATION_SERVICE_FEE_MARGIN`) `REGISTRATION_SERVICE_FEE_MARGIN`, ");
        sb.append(" 	SUM(a.`OTHER_SERVICE_FEE_MARGIN`) `OTHER_SERVICE_FEE_MARGIN`, ");
        sb.append(" 	SUM(a.`TOTAL_OTHER_INCOME_MARGIN`) `TOTAL_OTHER_INCOME_MARGIN`, ");
        sb.append(" 	SUM(a.`SALES_TRANSACTION_GP_PER`) `SALES_TRANSACTION_GP_PER`, ");
        sb.append(" 	SUM(a.`SALES_DEPT_HEADCOUNT`) `SALES_DEPT_HEADCOUNT`, ");
        sb.append(" 	SUM(a.`SALES_PRODUCTIBITY_PER_PERSON`) `SALES_PRODUCTIBITY_PER_PERSON`,");
        sb.append(" 	SUM(a.`ACTUAL_GPI_5`) `ACTUAL_GPI_5` ");
        sb.append(" from t_monthly_sale_rpt a  ");
        sb.append(" where a.STAT_YEAR =:year and a.MODEL <> '' and  a.PERMIT in ( ");
        sb.append(" 	select b.`许可_BMBS` from lsh经销商表 b ");
        if (!"LSH".equals(type)) {
            sb.append("     where b.大区 =:type or b.小区 =:type or b.CTA =:type or b.`许可_BMBS` =:type ");
        }
        sb.append(" ) GROUP BY a.MODEL,a.STAT_MONTH ");
        Map params = new HashMap();
        if (!"LSH".equals(type)) {
            params.put("type", type);
        }
        params.put("year", year);
        return this.getList(sb.toString(), params);
    }

    public List getWeekReport(String type,String date) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select  ");
        sb.append(" 	a.SEGMENT,a.CLASSIS,a.MODEL,a.MODEL_DE, ");
        sb.append(" 	SUM(a.CUSTOMER_ORDER_WEEKLY) CUSTOMER_ORDER_WEEKLY, ");
        sb.append(" 	SUM(a.CUSTOMER_ORDER_MTD) CUSTOMER_ORDER_MTD, ");
        sb.append(" 	SUM(a.CUSTOMER_ORDER_YTD) CUSTOMER_ORDER_YTD, ");
        sb.append(" 	SUM(a.CUSTOMER_ORDER_TOTAL_UNFULFILLED) CUSTOMER_ORDER_TOTAL_UNFULFILLED, ");
        sb.append(" 	SUM(a.RETAIL_SALE_WEEKLY) RETAIL_SALE_WEEKLY, ");
        sb.append(" 	SUM(a.RETAIL_SALE_MTD) RETAIL_SALE_MTD, ");
        sb.append(" 	SUM(a.RETAIL_SALE_YTD) RETAIL_SALE_YTD, ");
        sb.append(" 	SUM(a.STOCK_WITH_ORDER) STOCK_WITH_ORDER, ");
        sb.append(" 	SUM(a.STOCK_WITHOUT_ORDER) STOCK_WITHOUT_ORDER, ");
        sb.append(" 	SUM(a.STOCK_PENDING_FOR_INVOICE) STOCK_PENDING_FOR_INVOICE, ");
        sb.append(" 	SUM(a.ORDER_REQUIREMENT_PLAN) ORDER_REQUIREMENT_PLAN, ");
        sb.append(" 	SUM(a.oneJan) oneJan, ");
        sb.append(" 	SUM(a.oneFeb) oneFeb, ");
        sb.append(" 	SUM(a.oneMar) oneMar, ");
        sb.append(" 	SUM(a.oneApr) oneApr, ");
        sb.append(" 	SUM(a.oneMay) oneMay, ");
        sb.append(" 	SUM(a.oneJun) oneJun, ");
        sb.append(" 	SUM(a.oneJul) oneJul, ");
        sb.append(" 	SUM(a.oneAug) oneAug, ");
        sb.append(" 	SUM(a.oneSep) oneSep, ");
        sb.append(" 	SUM(a.oneOct) oneOct, ");
        sb.append(" 	SUM(a.oneNov) oneNov, ");
        sb.append(" 	SUM(a.oneDec) oneDec, ");
        sb.append(" 	SUM(a.tweJan) tweJan, ");
        sb.append(" 	SUM(a.tweFeb) tweFeb, ");
        sb.append(" 	SUM(a.tweMar) tweMar, ");
        sb.append(" 	SUM(a.tweApr) tweApr, ");
        sb.append(" 	SUM(a.tweMay) tweMay, ");
        sb.append(" 	SUM(a.tweJun) tweJun, ");
        sb.append(" 	SUM(a.tweJul) tweJul, ");
        sb.append(" 	SUM(a.tweAug) tweAug, ");
        sb.append(" 	SUM(a.tweSep) tweSep, ");
        sb.append(" 	SUM(a.tweOct) tweOct, ");
        sb.append(" 	SUM(a.tweNov) tweNov, ");
        sb.append(" 	SUM(a.tweDec) tweDec, ");
        sb.append(" 	SUM(a.threeJan) threeJan, ");
        sb.append(" 	SUM(a.threeFeb) threeFeb, ");
        sb.append(" 	SUM(a.threeMar) threeMar, ");
        sb.append(" 	SUM(a.threeApr) threeApr, ");
        sb.append(" 	SUM(a.threeMay) threeMay, ");
        sb.append(" 	SUM(a.threeJun) threeJun, ");
        sb.append(" 	SUM(a.threeJul) threeJul, ");
        sb.append(" 	SUM(a.threeAug) threeAug, ");
        sb.append(" 	SUM(a.threeSep) threeSep, ");
        sb.append(" 	SUM(a.threeOct) threeOct, ");
        sb.append(" 	SUM(a.threeNov) threeNov, ");
        sb.append(" 	SUM(a.threeDec) threeDec ," +
                "  SUM( a.WHOLESALE_NOTINTO_STORAGE) WHOLESALE_NOTINTO_STORAGE, " +
                " SUM( a.RETAIL_SALE_WITHOUT_GIVECAR)  RETAIL_SALE_WITHOUT_GIVECAR");
        sb.append(" from ( ");
        sb.append(" select  ");
        sb.append(" 	a.permit,a.SEGMENT,a.CLASSIS,a.MODEL,a.MODEL_DE, ");
        sb.append(" 	a.CUSTOMER_ORDER_WEEKLY, ");
        sb.append(" 	a.`CUSTOMER_ORDER_MTD`, ");
        sb.append(" 	a.`CUSTOMER_ORDER_YTD`, ");
        sb.append(" 	a.`CUSTOMER_ORDER_TOTAL_UNFULFILLED`, ");
        sb.append(" 	a.`RETAIL_SALE_WEEKLY`, ");
        sb.append(" 	a.`RETAIL_SALE_MTD`, ");
        sb.append(" 	a.`RETAIL_SALE_YTD`, ");
        sb.append(" 	a.`STOCK_WITH_ORDER`, ");
        sb.append(" 	a.`STOCK_WITHOUT_ORDER`, ");
        sb.append(" 	a.`STOCK_PENDING_FOR_INVOICE`, ");
        sb.append(" 	a.ORDER_REQUIREMENT_PLAN, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_1,'|',1) oneJan, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_1,'|',2) oneFeb, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_1,'|',3) oneMar, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_1,'|',4) oneApr, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_1,'|',5) oneMay, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_1,'|',6) oneJun,  ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_1,'|',7) oneJul, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_1,'|',8) oneAug, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_1,'|',9) oneSep, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_1,'|',10) oneOct, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_1,'|',11) oneNov, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_1,'|',12) oneDec, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_2,'|',1) tweJan, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_2,'|',2) tweFeb, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_2,'|',3) tweMar, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_2,'|',4) tweApr, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_2,'|',5) tweMay, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_2,'|',6) tweJun,  ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_2,'|',7) tweJul, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_2,'|',8) tweAug, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_2,'|',9) tweSep, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_2,'|',10) tweOct, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_2,'|',11) tweNov, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_2,'|',12) tweDec, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_3,'|',1) threeJan, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_3,'|',2) threeFeb, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_3,'|',3) threeMar, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_3,'|',4) threeApr, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_3,'|',5) threeMay, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_3,'|',6) threeJun,  ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_3,'|',7) threeJul, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_3,'|',8) threeAug, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_3,'|',9) threeSep, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_3,'|',10) threeOct, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_3,'|',11) threeNov, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_3,'|',12) threeDec ," +
                "    a.WHOLESALE_NOTINTO_STORAGE, " +
                " a.RETAIL_SALE_WITHOUT_GIVECAR");
        sb.append(" from t_weekly_sm2_ros_rpt a where   a.UPDATE_DATE=:date ");
        sb.append(" )  a ");
        sb.append(" where   a.PERMIT in ( ");
        sb.append(" 	select b.`许可_BMBS` from lsh经销商表 b ");
        if (!"LSH".equals(type)) {
            sb.append(" where b.大区 =:type or b.小区 =:type or b.CTA =:type or b.`经销商名`=:type ");
        }
        sb.append(" ) GROUP BY a.SEGMENT,a.CLASSIS,a.MODEL,a.MODEL_DE ");
        Map params = new HashMap();
        if (!"LSH".equals(type)) {
            params.put("type", type);
        }
        params.put("date",date);
        return this.getList(sb.toString(), params);
    }


    public List getWeekReportOne(String type,String date) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select  ");
        sb.append(" 	a.SEGMENT,a.CLASSIS,a.MODEL,a.MODEL_DE, ");
        sb.append(" 	SUM(a.CUSTOMER_ORDER_WEEKLY) CUSTOMER_ORDER_WEEKLY, ");
        sb.append(" 	SUM(a.CUSTOMER_ORDER_MTD) CUSTOMER_ORDER_MTD, ");
        sb.append(" 	SUM(a.CUSTOMER_ORDER_YTD) CUSTOMER_ORDER_YTD, ");
        sb.append(" 	SUM(a.CUSTOMER_ORDER_TOTAL_UNFULFILLED) CUSTOMER_ORDER_TOTAL_UNFULFILLED, ");
        sb.append(" 	SUM(a.RETAIL_SALE_WEEKLY) RETAIL_SALE_WEEKLY, ");
        sb.append(" 	SUM(a.RETAIL_SALE_MTD) RETAIL_SALE_MTD, ");
        sb.append(" 	SUM(a.RETAIL_SALE_YTD) RETAIL_SALE_YTD, ");
        sb.append(" 	SUM(a.STOCK_WITH_ORDER) STOCK_WITH_ORDER, ");
        sb.append(" 	SUM(a.STOCK_WITHOUT_ORDER) STOCK_WITHOUT_ORDER, ");
        sb.append(" 	SUM(a.STOCK_PENDING_FOR_INVOICE) STOCK_PENDING_FOR_INVOICE, ");
        sb.append(" 	SUM(a.ORDER_REQUIREMENT_PLAN) ORDER_REQUIREMENT_PLAN, ");
        sb.append(" 	SUM(a.oneJan) oneJan, ");
        sb.append(" 	SUM(a.oneFeb) oneFeb, ");
        sb.append(" 	SUM(a.oneMar) oneMar, ");
        sb.append(" 	SUM(a.oneApr) oneApr, ");
        sb.append(" 	SUM(a.oneMay) oneMay, ");
        sb.append(" 	SUM(a.oneJun) oneJun, ");
        sb.append(" 	SUM(a.oneJul) oneJul, ");
        sb.append(" 	SUM(a.oneAug) oneAug, ");
        sb.append(" 	SUM(a.oneSep) oneSep, ");
        sb.append(" 	SUM(a.oneOct) oneOct, ");
        sb.append(" 	SUM(a.oneNov) oneNov, ");
        sb.append(" 	SUM(a.oneDec) oneDec, ");
        sb.append(" 	SUM(a.tweJan) tweJan, ");
        sb.append(" 	SUM(a.tweFeb) tweFeb, ");
        sb.append(" 	SUM(a.tweMar) tweMar, ");
        sb.append(" 	SUM(a.tweApr) tweApr, ");
        sb.append(" 	SUM(a.tweMay) tweMay, ");
        sb.append(" 	SUM(a.tweJun) tweJun, ");
        sb.append(" 	SUM(a.tweJul) tweJul, ");
        sb.append(" 	SUM(a.tweAug) tweAug, ");
        sb.append(" 	SUM(a.tweSep) tweSep, ");
        sb.append(" 	SUM(a.tweOct) tweOct, ");
        sb.append(" 	SUM(a.tweNov) tweNov, ");
        sb.append(" 	SUM(a.tweDec) tweDec, ");
        sb.append(" 	SUM(a.threeJan) threeJan, ");
        sb.append(" 	SUM(a.threeFeb) threeFeb, ");
        sb.append(" 	SUM(a.threeMar) threeMar, ");
        sb.append(" 	SUM(a.threeApr) threeApr, ");
        sb.append(" 	SUM(a.threeMay) threeMay, ");
        sb.append(" 	SUM(a.threeJun) threeJun, ");
        sb.append(" 	SUM(a.threeJul) threeJul, ");
        sb.append(" 	SUM(a.threeAug) threeAug, ");
        sb.append(" 	SUM(a.threeSep) threeSep, ");
        sb.append(" 	SUM(a.threeOct) threeOct, ");
        sb.append(" 	SUM(a.threeNov) threeNov, ");
        sb.append(" 	SUM(a.threeDec) threeDec ," +
                " SUM( a.WHOLESALE_NOTINTO_STORAGE) WHOLESALE_NOTINTO_STORAGE, " +
                " SUM( a.RETAIL_SALE_WITHOUT_GIVECAR)  RETAIL_SALE_WITHOUT_GIVECAR ");
        sb.append(" from ( ");
        sb.append(" select  ");
        sb.append(" 	a.permit,a.SEGMENT,a.CLASSIS,a.MODEL,a.MODEL_DE, ");
        sb.append(" 	a.CUSTOMER_ORDER_WEEKLY, ");
        sb.append(" 	a.`CUSTOMER_ORDER_MTD`, ");
        sb.append(" 	a.`CUSTOMER_ORDER_YTD`, ");
        sb.append(" 	a.`CUSTOMER_ORDER_TOTAL_UNFULFILLED`, ");
        sb.append(" 	a.`RETAIL_SALE_WEEKLY`, ");
        sb.append(" 	a.`RETAIL_SALE_MTD`, ");
        sb.append(" 	a.`RETAIL_SALE_YTD`, ");
        sb.append(" 	a.`STOCK_WITH_ORDER`, ");
        sb.append(" 	a.`STOCK_WITHOUT_ORDER`, ");
        sb.append(" 	a.`STOCK_PENDING_FOR_INVOICE`, ");
        sb.append(" 	a.ORDER_REQUIREMENT_PLAN, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_1,'|',1) oneJan, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_1,'|',2) oneFeb, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_1,'|',3) oneMar, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_1,'|',4) oneApr, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_1,'|',5) oneMay, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_1,'|',6) oneJun,  ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_1,'|',7) oneJul, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_1,'|',8) oneAug, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_1,'|',9) oneSep, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_1,'|',10) oneOct, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_1,'|',11) oneNov, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_1,'|',12) oneDec, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_2,'|',1) tweJan, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_2,'|',2) tweFeb, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_2,'|',3) tweMar, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_2,'|',4) tweApr, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_2,'|',5) tweMay, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_2,'|',6) tweJun,  ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_2,'|',7) tweJul, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_2,'|',8) tweAug, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_2,'|',9) tweSep, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_2,'|',10) tweOct, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_2,'|',11) tweNov, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_2,'|',12) tweDec, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_3,'|',1) threeJan, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_3,'|',2) threeFeb, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_3,'|',3) threeMar, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_3,'|',4) threeApr, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_3,'|',5) threeMay, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_3,'|',6) threeJun,  ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_3,'|',7) threeJul, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_3,'|',8) threeAug, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_3,'|',9) threeSep, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_3,'|',10) threeOct, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_3,'|',11) threeNov, ");
        sb.append(" 	str_split(a.ALLOCATION_YEAR_3,'|',12) threeDec ," +
                "    a.WHOLESALE_NOTINTO_STORAGE, " +
                " a.RETAIL_SALE_WITHOUT_GIVECAR");
        sb.append(" from t_weekly_sm2_ros_rpt a " +
                "  JOIN (\n" +
                " SELECT\n" +
                " a.`许可_BMBS` permit\n" +
                " FROM\n" +
                " lsh经销商表 a \n" +
                " JOIN (select a.SalesCode from  lsh经销商表 a  where  a.`许可_BMBS`=:type ) b on a.SalesCode=b.SalesCode\n" +
                " ) b on a.PERMIT=b.permit\n  ");

        sb.append(" where a.UPDATE_DATE=:date )  a ");
        sb.append("   GROUP BY a.SEGMENT,a.CLASSIS,a.MODEL,a.MODEL_DE ");
        Map params = new HashMap();
        params.put("type", type);
        params.put("date",date);
        return this.getList(sb.toString(), params);
    }

    @Override
    public List getSmTwoWeekReportOne(String type, String date) {
        StringBuffer sql = new StringBuffer("" +
                "  SELECT" +
                "  kc.`采购批发商` AS 许可," +
                "  CONCAT( cx.`分类`  , '_', cx.`车型_DE`  , '_', cx.`底盘`   , '_', cx.`车型`  ) AS uniqueCode," +
                "  cx.`分类` SEGMENT, cx.`车型_DE` MODEL_DE, cx.`底盘` CLASSIS, cx.`车型` MODEL," +
                "  sum( IF ( MONTH (kc.`批发日期`) = 1, 1, 0 )) oneJan," +
                "  sum( IF ( MONTH (kc.`批发日期`) = 2, 1, 0 )) oneFeb, " +
                "  sum( IF ( MONTH (kc.`批发日期`) = 3, 1, 0 )) oneMar, " +
                "  sum( IF ( MONTH (kc.`批发日期`) = 4, 1, 0 )) oneApr," +
                "  sum( IF ( MONTH (kc.`批发日期`) = 5, 1, 0 )) oneMay, " +
                "  sum( IF ( MONTH (kc.`批发日期`) = 6, 1, 0 )) oneJun, " +
                "  sum( IF ( MONTH (kc.`批发日期`) = 7, 1, 0 )) oneJul, " +
                "  sum( IF ( MONTH (kc.`批发日期`) = 8, 1, 0 )) oneAug," +
                "  sum( IF ( MONTH (kc.`批发日期`) = 9, 1, 0 )) oneSep," +
                "  sum( IF ( MONTH (kc.`批发日期`) = 10, 1, 0 )) oneOct, " +
                "  sum( IF ( MONTH (kc.`批发日期`) = 11, 1, 0 )) oneNov, " +
                "  sum( IF ( MONTH (kc.`批发日期`) = 12, 1, 0 )) oneDeco" +
                " FROM" +
                " `lsh车型` AS cx" +
                " LEFT JOIN (" +
                "       SELECT * FROM `lsh库存表` kc" +
                "        JOIN (" +
                "            SELECT a.`许可_BMBS` permit FROM lsh经销商表 a JOIN ( SELECT a.SalesCode FROM lsh经销商表 a WHERE a.`许可_BMBS` =:type) b ON a.SalesCode = b.SalesCode" +
                "          ) b ON kc.采购批发商 = b.permit" +
                "        where    kc.`车架号` NOT LIKE 'IRISTechnologies%'" +
                "            AND kc.`批发日期` IS NOT NULL             " +
                "            AND YEAR (kc.`批发日期`) = YEAR (:date)" +
                "     ) AS kc ON  " +
                "     kc.`车型编号` = cx.`车型编号`     " +
                " WHERE" +
                " cx.`车型编号` <> 0" +
                " AND cx.分类 IS NOT NULL" +
                " GROUP BY" +
                " cx.`分类`," +
                " cx.`车型_DE`," +
                " cx.`底盘`," +
                " cx.`车型`  ");
        Map params = new HashMap();
        params.put("type", type);
        params.put("date", date);
        return this.getList(sql.toString(), params);
    }

    @Override
    public List getFIRptOne(String permit, String year) {
        String sql = "select * from t_fi_performance_tracking_rpt where syear=:syear and permit=:permit ";
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("syear", year);
        return this.getList(sql, params);
    }

    @Override
    public List getGroupReportByPermitAndYear(String permit, String year) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select a.MODEL,a.STAT_MONTH, ");
        sb.append(" 	SUM(a.`SIGNED_WHOLESALE_TA`) `SIGNED_WHOLESALE_TA`, ");
        sb.append(" 	SUM(a.`ACTUAL_WHOLESALE`) `ACTUAL_WHOLESALE`, ");
        sb.append(" 	SUM(a.`WS_ACHIEVEMENT`) `WS_ACHIEVEMENT`, ");
        sb.append(" 	SUM(a.`SIGNED_RETAIL_TA`) `SIGNED_RETAIL_TA`, ");
        sb.append(" 	SUM(a.`ACTUAL_RETAIL_NBI`) `ACTUAL_RETAIL_NBI`, ");
        sb.append(" 	SUM(a.`NBI_RT_ACHIEVEMENT`) `NBI_RT_ACHIEVEMENT`, ");
        sb.append(" 	SUM(a.`ROS_RETAIL_TA`) `ROS_RETAIL_TA`, ");
        sb.append(" 	SUM(a.`ACTUAL_RETAIL_ROS`) `ACTUAL_RETAIL_ROS`, ");
        sb.append(" 	SUM(a.`ROS_RT_ACHIEVEMENT`) `ROS_RT_ACHIEVEMENT`, ");
        sb.append(" 	SUM(a.`REMAINING_PRE_REPORT`) `REMAINING_PRE_REPORT`, ");
        sb.append(" 	SUM(a.`SALES_GUIDELINE`) `SALES_GUIDELINE`, ");
        sb.append(" 	SUM(a.`LT_89_DAYS_STOCK`) `LT_89_DAYS_STOCK`, ");
        sb.append(" 	SUM(a.`90_149_DAYS_STOCK`) `90_149_DAYS_STOCK`, ");
        sb.append(" 	SUM(a.`GET_150_DAYS_STOCK`) `GET_150_DAYS_STOCK`, ");
        sb.append(" 	SUM(a.`CLOSING_STOCK`) `CLOSING_STOCK`, ");
        sb.append(" 	SUM(a.`STOCK_LEVEL`) `STOCK_LEVEL`, ");
        sb.append(" 	SUM(a.`AGING_STOCK_GT_90_DAYS`) `AGING_STOCK_GT_90_DAYS`, ");
        sb.append(" 	SUM(a.`AGING_STOCK_GT_90_DAYS_PER`) `AGING_STOCK_GT_90_DAYS_PER`, ");
        sb.append(" 	SUM(a.`NEW_CAR_EXTERNAL_SALES_REVENUE`) `NEW_CAR_EXTERNAL_SALES_REVENUE`, ");
        sb.append(" 	SUM(a.`ACTUAL_GPI`) `ACTUAL_GPI`, ");
        sb.append(" 	SUM(a.`ACTUAL_GPII`) `ACTUAL_GPII`, ");
        sb.append(" 	SUM(a.`ACTUAL_SI`) `ACTUAL_SI`, ");
        sb.append(" 	SUM(a.`ACTUAL_GPIII`) `ACTUAL_GPIII`, ");
        sb.append(" 	SUM(a.`GPI_PER`) `GPI_PER`, ");
        sb.append(" 	SUM(a.`GPII_PER`) `GPII_PER`, ");
        sb.append(" 	SUM(a.`SI_PER`) `SI_PER`, ");
        sb.append(" 	SUM(a.`GPIII_PER`) `GPIII_PER`, ");
        sb.append(" 	SUM(a.`BUDGET_SALES_DIRECT_EXPENSES`) `BUDGET_SALES_DIRECT_EXPENSES`, ");
        sb.append(" 	SUM(a.`ACTUAL_SALES_DIRECT_EXPENSES`) `ACTUAL_SALES_DIRECT_EXPENSES`, ");
        sb.append(" 	SUM(a.`ACTUAL_DIRECT_EXPENSES_PER_CAR`) `ACTUAL_DIRECT_EXPENSES_PER_CAR`, ");
        sb.append(" 	SUM(a.`BUDGET_SALES_INDIRECT_EXPENSES`) `BUDGET_SALES_INDIRECT_EXPENSES`, ");
        sb.append(" 	SUM(a.`ACTUAL_SALES_INDIRECT_EXPENSES`) `ACTUAL_SALES_INDIRECT_EXPENSES`, ");
        sb.append(" 	SUM(a.`ACTUAL_INDIRECT_EXPENSES_PER_CAR`) `ACTUAL_INDIRECT_EXPENSES_PER_CAR`, ");
        sb.append(" 	SUM(a.`ACTUAL_TOTAL_EXPENSES_PER_CAR`) `ACTUAL_TOTAL_EXPENSES_PER_CAR`, ");
        sb.append(" 	SUM(a.`USED_CAR_APPRAISAL_VOLUME`) `USED_CAR_APPRAISAL_VOLUME`, ");
        sb.append(" 	SUM(a.`APPRAISAL`) `APPRAISAL`, ");
        sb.append(" 	SUM(a.`USED_CAR_TRADE_IN_VOLUME`) `USED_CAR_TRADE_IN_VOLUME`, ");
        sb.append(" 	SUM(a.`TRADE_IN_PER`) `TRADE_IN_PER`, ");
        sb.append(" 	SUM(a.`TRADE_IN_COMMISSION`) `TRADE_IN_COMMISSION`, ");
        sb.append(" 	SUM(a.`TRADE_IN_COMMISSION_PER_CAR`) `TRADE_IN_COMMISSION_PER_CAR`, ");
        sb.append(" 	SUM(a.`AFTER_MARKET_PRODUCT_REVENUE`) `AFTER_MARKET_PRODUCT_REVENUE`, ");
        sb.append(" 	SUM(a.`AFTER_MARKET_PRODUCT_REVENUE_PER_CAR`) `AFTER_MARKET_PRODUCT_REVENUE_PER_CAR`, ");
        sb.append(" 	SUM(a.`AFTER_MARKET_PRODUCT_GP`) `AFTER_MARKET_PRODUCT_GP`, ");
        sb.append(" 	SUM(a.`AFTER_MARKET_PRODUCT_GP_PER`) `AFTER_MARKET_PRODUCT_GP_PER`, ");
        sb.append(" 	SUM(a.`NEW_CAR_INSURANCE`) `NEW_CAR_INSURANCE`, ");
        sb.append(" 	SUM(a.`NEW_CAR_INSURANCE_RATE`) `NEW_CAR_INSURANCE_RATE`, ");
        sb.append(" 	SUM(a.`NEW_CAR_INSURANCE_COMMISSION`) `NEW_CAR_INSURANCE_COMMISSION`, ");
        sb.append(" 	SUM(a.`NEW_CAR_FINANCING`) `NEW_CAR_FINANCING`, ");
        sb.append(" 	SUM(a.`FINANCING_RATE`) `FINANCING_RATE`, ");
        sb.append(" 	SUM(a.`NEW_CAR_AGILITY`) `NEW_CAR_AGILITY`, ");
        sb.append(" 	SUM(a.`AGILITY_RATE`) `AGILITY_RATE`, ");
        sb.append(" 	SUM(a.`FINANCING_COMMISSION`) `FINANCING_COMMISSION`, ");
        sb.append(" 	SUM(a.`FINANCING_SERVICE_FEE_MARGIN`) `FINANCING_SERVICE_FEE_MARGIN`, ");
        sb.append(" 	SUM(a.`FINANCING_SERVICE_FEE_MARGIN_PER_CAR`) `FINANCING_SERVICE_FEE_MARGIN_PER_CAR`, ");
        sb.append(" 	SUM(a.`REGISTRATION_SERVICE_FEE_MARGIN`) `REGISTRATION_SERVICE_FEE_MARGIN`, ");
        sb.append(" 	SUM(a.`OTHER_SERVICE_FEE_MARGIN`) `OTHER_SERVICE_FEE_MARGIN`, ");
        sb.append(" 	SUM(a.`TOTAL_OTHER_INCOME_MARGIN`) `TOTAL_OTHER_INCOME_MARGIN`, ");
        sb.append(" 	SUM(a.`SALES_TRANSACTION_GP_PER`) `SALES_TRANSACTION_GP_PER`, ");
        sb.append(" 	SUM(a.`SALES_DEPT_HEADCOUNT`) `SALES_DEPT_HEADCOUNT`, ");
        sb.append(" 	SUM(a.`SALES_PRODUCTIBITY_PER_PERSON`) `SALES_PRODUCTIBITY_PER_PERSON`,");
        sb.append(" 	SUM(a.`ACTUAL_GPI_5`) `ACTUAL_GPI_5` ");
        sb.append(" from t_monthly_sale_rpt a  ");
        sb.append(" where a.STAT_YEAR =:year and a.MODEL <> '' and  a.PERMIT in ( ");
        sb.append(" 	select b.`许可_BMBS` from lsh经销商表 b ");
        sb.append("     where b.许可_LSH =:permit ");
        sb.append(" ) GROUP BY a.MODEL,a.STAT_MONTH ");
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("year", year);
        return this.getList(sb.toString(), params);
    }

    @Override
    public List<Map<String,Object>> getCluesListDate(String dealer, String startTime,String endTime) {
        String sql = "select \n" +
                "  IFNULL(`cus_name`,'') '线索姓名',\n" +
                "  IFNULL(`tel`,'') '电话号码',\n" +
                "  IFNULL(`created_date`,'') '线索创建日期',\n" +
                " IFNULL( `source`,'') '来源渠道',\n" +
                " IFNULL( `cus_level`,'') '意向级别',\n" +
                "  IFNULL(`car_type`,'')  '意向车型',\n" +
                "  IFNULL(`sc_name`,'')  '销售顾问',\n" +
                " IFNULL( `sc_group`,'')  '销售组',\n" +
                " IFNULL( `follow_update`,'')  '跟进日期',\n" +
                "  IFNULL(`now_duration`,'')  '当天跟进通话时长',\n" +
                " IFNULL( `now_remark`,'')  '当天跟进备注',\n" +
                " IFNULL( `old_date`,'')  '上一次跟进日期',\n" +
                " IFNULL( `old_duration`,'')  '上一次跟进通话时长',\n" +
                " IFNULL( `old_remark`,'')  '上一次跟进备注',\n" +
                " IFNULL( `old_in_date`,'')  '最近一次进店日期',\n" +
                " IFNULL( `order_date`,'')  '订单日期',\n" +
                " IFNULL( `invoice_date`,'')  '开票日期'\n" +
                "from `t_flowup_report` where `follow_update` BETWEEN :startTime and :endTime and `permit` = :permit order by `follow_update` desc";
        Map params = Maps.newHashMap();
        params.put("permit",dealer);
        params.put("startTime",startTime);
        params.put("endTime",endTime);
        return (List<Map<String,Object>>)this.getList(sql,params);


    }

    @Override
    public List<Map<String, Object>> getCurrentCluesList(String dealer, String time) {                  //少一个跟进日期
        Map params = Maps.newHashMap();

        params.put("date", time);
        StringBuffer sqlStr = new StringBuffer();

        sqlStr.append(" SELECT * FROM ( ");

        sqlStr.append("SELECT  " +
                " a.`许可`, b.`顾客姓名` '线索姓名', a.`电话号码` '电话号码',b.`留档日期` '线索创建日期',  " +
                " b.`顾客首次渠道` '来源渠道', a.`销售顾问` '销售顾问ID', a.duration '当天跟进通话时长',   " +
                "  a.`跟进备注` '当天跟进备注', j.`上次跟进日期` '上一次跟进日期', j.`上次通话时长` '上一次跟进通话时长',  " +
                " j.`上次跟进备注` '上一次跟进备注', b.`上次进店日期` '最近一次进店日期', a.`更新时间` '更新时间',  " +
                " c.`车型` '意向车型', b.`新顾客级别` '意向级别', d.`人员姓名` '销售顾问',  " +
                " e.`组名` '销售组', h.`订单申请日期` AS '订单日期', DATE_FORMAT(i.`开票时间`, '%Y-%m-%d') AS '开票日期', CURDATE() '跟进日期'  " +
                "FROM  " +
                " `顾客记录汇总` AS b RIGHT JOIN (SELECT a.id, a.`许可`, a.`电话号码`,  " +
                "  a.`销售顾问`, a.`跟进备注`, a.`更新时间`, b.duration, a.`模式`,  " +
                "  a.`跟进日期` FROM  `跟进记录表` a, sc_call_history b  " +
                " WHERE  a.callId = b.id  AND a.`跟进日期` =   :date ) AS a ON b.`电话号码` = a.`电话号码`  " +
                "AND b.`许可` = a.`许可`  " +
                "LEFT JOIN `lsh车型` c ON b.`意向车型A` = c.`车型编号`  " +
                "LEFT JOIN `用户` AS d ON d.`用户名` = b.`销售顾问`  " +
                "LEFT JOIN `销售顾问分组` AS e ON e.`销售顾问` = d.`用户名`  " +
                "LEFT JOIN vw_max_quotedinfo_all g ON b.`顾客编号` = g.顾客编号 " +
                "LEFT JOIN `报价订单表` AS h ON h.`订单编号` = g.`订单编号`  " +
                "LEFT JOIN `报价交车表` AS i ON i.`编号` = g.`交车`  " +
                "LEFT JOIN ( SELECT b.id, b.`许可`, b.`电话号码`, b.`销售顾问`, b.`跟进日期`, b.`跟进结果`, " +
                " b.`下次跟进时间`, b.`更新时间`, b.`跟进备注`, c.`跟进日期` 上次跟进日期, c.`跟进备注` 上次跟进备注," +
                " d.duration 上次通话时长 FROM ( SELECT a.id, MAX(b.id) lastId FROM 跟进记录表 a LEFT JOIN 跟进记录表 b ON " +
                " ( a.`电话号码` = b.`电话号码` AND a.`更新时间` > b.`更新时间` ) WHERE a.`跟进日期` = :date GROUP BY a.id ) " +
                " a JOIN 跟进记录表 b ON a.id = b.id LEFT JOIN 跟进记录表 c ON a.lastId = c.id LEFT JOIN sc_call_history d " +
                " ON c.callId = d.id ) AS j ON (  " +
                " j.`许可` = a.`许可` AND j.`电话号码` = a.`电话号码` AND j.id = a.id )  " +
                " WHERE a.`模式` = '跟进' AND a.`跟进日期` =   :date " );
        if (!Strings.isNullOrEmpty(dealer)){
            params.put("permit", dealer);
            sqlStr.append(" AND b.`许可`=:permit  ") ;
        }
        sqlStr.append( " UNION ALL  SELECT  " +
                "  a.`许可`,  b.`顾客姓名` '线索姓名',  a.`电话号码` '电话号码',  " +
                "  b.`线索创建日期` '线索创建日期',  b.`渠道` '来源渠道',  " +
                "  a.`销售顾问` '销售顾问ID',  a.duration '当天跟进通话时长',  " +
                "  a.`跟进备注` '当天跟进备注',  j.`上次跟进日期` '上一次跟进日期',  " +
                "  j.`上次通话时长` '上一次跟进通话时长',  j.`上次跟进备注` '上一次跟进备注',  " +
                "  '' AS '最近一次进店日期',  a.`更新时间` '更新时间',  " +
                "  c.`车型` '意向车型',  b.`客户级别` '意向级别',  " +
                "  d.`人员姓名` '销售顾问',  e.`组名` '销售组',  " +
                "	h.`订单申请日期` AS '订单日期'," +
                "	DATE_FORMAT(i.`开票时间`, '%Y-%m-%d') AS '开票日期', CURDATE() '跟进日期'  " +
                " FROM (SELECT * FROM `lsh导入名单` bb WHERE bb.`是否有效线索`='是' AND bb.`是否线索`='是' ");
        if (!Strings.isNullOrEmpty(dealer)){
            sqlStr.append(" AND bb.许可= :permit " ) ;
        }
        sqlStr.append(" ) AS b RIGHT JOIN (  SELECT  " +
                "  a.id, a.`许可`,   a.`电话号码`,   a.`销售顾问`,  " +
                "   a.`跟进备注`,   a.`更新时间`,  " +
                "   b.duration,   a.`模式`,   a.`跟进日期`  " +
                "  FROM   `跟进记录表` a,   sc_call_history b  " +
                "  WHERE   a.callId = b.id  AND a.`跟进日期` =   :date  " +
                " ) AS a ON b.`电话号码` = a.`电话号码`  " +
                " AND b.`许可` = a.`许可`  " +
                " LEFT JOIN `lsh车型` AS c ON c.`车型编号` = b.`车型编号`  " +
                " LEFT JOIN `用户` AS d ON d.`用户名` = a.`销售顾问`  " +
                " LEFT JOIN `销售顾问分组` AS e ON e.`销售顾问` = d.`用户名`  " +
                " LEFT JOIN ( SELECT b.id, b.`许可`, b.`电话号码`, b.`销售顾问`, b.`跟进日期`, " +
                " b.`跟进结果`, b.`下次跟进时间`, b.`更新时间`, b.`跟进备注`, c.`跟进日期` 上次跟进日期, c.`跟进备注` 上次跟进备注, " +
                " d.duration 上次通话时长 FROM ( SELECT a.id, MAX(b.id) lastId FROM 跟进记录表 a LEFT JOIN 跟进记录表 b ON " +
                " ( a.`电话号码` = b.`电话号码` AND a.`更新时间` > b.`更新时间` ) WHERE a.`跟进日期` = :date GROUP BY a.id ) a JOIN 跟进记录表 b ON a.id = b.id " +
                " LEFT JOIN 跟进记录表 c ON a.lastId = c.id LEFT JOIN sc_call_history d ON c.callId = d.id) AS j ON (  " +
                "  j.`许可` = a.`许可`  AND j.`电话号码` = a.`电话号码` AND j.id = a.id )  " +
                "LEFT JOIN vw_max_quotedinfo_all g ON b.`顾客编号` = g.顾客编号 " +
                "LEFT JOIN `报价订单表` AS h ON h.`订单编号` = g.`订单编号`  " +
                "LEFT JOIN `报价交车表` AS i ON i.`编号` = g.`交车`  " +
                " WHERE  a.`模式` = '邀约'  AND a.`跟进日期` =   :date " );
        if (!Strings.isNullOrEmpty(dealer)){
            sqlStr.append(" AND b.`许可`=:permit  ") ;
        }
        sqlStr.append(" UNION ALL  " +
                "  SELECT  " +
                "   a.`许可`,   b.`顾客姓名` '线索姓名',  " +
                "   a.`电话号码` '电话号码',   b.`留档日期` '线索创建日期',  " +
                "   b.`顾客首次渠道` '来源渠道',   a.`销售顾问` '销售顾问ID',  " +
                "   a.duration '当天跟进通话时长',   a.`跟进备注` '当天跟进备注',   j.`上次跟进日期` '上一次跟进日期',  " +
                "   j.`上次通话时长` '上一次跟进通话时长',   j.`上次跟进备注` '上一次跟进备注',  " +
                "   b.`上次进店日期` '最近一次进店日期',   a.`更新时间` '更新时间',  " +
                "   c.`车型` '意向车型',   b.`新顾客级别` '意向级别',  " +
                "   d.`人员姓名` '销售顾问',   e.`组名` '销售组'," +
                "	h.`订单申请日期` AS '订单日期'," +
                "	DATE_FORMAT(i.`开票时间`, '%Y-%m-%d') AS '开票日期',  CURDATE() '跟进日期'  " +
                "  FROM   `顾客记录汇总` AS b  RIGHT JOIN (  " +
                "   SELECT   a.id, a.`许可`,    a.`电话号码`,  " +
                "    a.`销售顾问`,    a.`跟进备注`,    a.`更新时间`,  " +
                "    b.duration,    a.`模式`,    a.`跟进日期`  " +
                "   FROM    `跟进记录表` a  " +
                "   LEFT JOIN sc_call_history b ON a.callId = b.id  " +
                "   WHERE    ISNULL(b.id)  " +
                "   AND a.`跟进日期` =   :date  " +
                "  ) AS a ON b.`电话号码` = a.`电话号码`  " +
                "  AND b.`许可` = a.`许可`  " +
                "  LEFT JOIN `lsh车型` c ON b.`意向车型A` = c.`车型编号`  " +
                "  LEFT JOIN `用户` AS d ON d.`用户名` = b.`销售顾问`  " +
                "  LEFT JOIN `销售顾问分组` AS e ON e.`销售顾问` = d.`用户名`  " +
                "  LEFT JOIN ( SELECT b.id, b.`许可`, b.`电话号码`, b.`销售顾问`, b.`跟进日期`, b.`跟进结果`, b.`下次跟进时间`, " +
                " b.`更新时间`, b.`跟进备注`, c.`跟进日期` 上次跟进日期, c.`跟进备注` 上次跟进备注, d.duration 上次通话时长" +
                "  FROM ( SELECT a.id, MAX(b.id) lastId FROM 跟进记录表 a LEFT JOIN 跟进记录表 b " +
                " ON ( a.`电话号码` = b.`电话号码` AND a.`更新时间` > b.`更新时间` ) WHERE a.`跟进日期` = :date GROUP BY a.id ) a " +
                " JOIN 跟进记录表 b ON a.id = b.id LEFT JOIN 跟进记录表 c ON a.lastId = c.id LEFT JOIN sc_call_history d ON c.callId = d.id) AS j ON (  " +
                "   j.`许可` = a.`许可`  AND j.`电话号码` = a.`电话号码` AND j.id = a.id  )  " +
                "LEFT JOIN vw_max_quotedinfo_all g ON b.`顾客编号` = g.顾客编号 " +
                "LEFT JOIN `报价订单表` AS h ON h.`订单编号` = g.`订单编号`  " +
                "LEFT JOIN `报价交车表` AS i ON i.`编号` = g.`交车`  " +
                "  WHERE   a.`模式` = '跟进'  " +
                "  AND a.`跟进日期` =   :date " );
        if (!Strings.isNullOrEmpty(dealer)){
            sqlStr.append(" AND b.`许可`=:permit  ") ;
        }

        sqlStr.append("  UNION ALL  " +
                "   SELECT  " +
                "    a.`许可`,    b.`顾客姓名` '线索姓名',  " +
                "    a.`电话号码` '电话号码',   b.`线索创建日期` '线索创建日期',  " +
                "    b.`渠道` '来源渠道',   a.`销售顾问` '销售顾问ID',  " +
                "    a.duration '当天跟进通话时长',   a.`跟进备注` '当天跟进备注',  " +
                "    j.`上次跟进日期` '上一次跟进日期',   j.`上次通话时长` '上一次跟进通话时长',  " +
                "    j.`上次跟进备注` '上一次跟进备注',  '' AS '最近一次进店日期',   " +
                "      a.`更新时间` '更新时间',  c.`车型` '意向车型',  " +
                "    b.`客户级别` '意向级别',    d.`人员姓名` '销售顾问',  " +
                "    e.`组名` '销售组'," +
                "	h.`订单申请日期` AS '订单日期'," +
                "	DATE_FORMAT(i.`开票时间`, '%Y-%m-%d') AS '开票日期', " +
                "    CURDATE() '跟进日期'  FROM  (SELECT * FROM `lsh导入名单` bb WHERE bb.`是否有效线索`='是' AND bb.`是否线索`='是' ");
        if (!Strings.isNullOrEmpty(dealer)){
            sqlStr.append(" AND bb.许可= :permit ") ;
        }

        sqlStr.append( "  ) AS b  RIGHT JOIN (  SELECT  a.id, a.`许可`,  " +
                "     a.`电话号码`,   a.`销售顾问`,   a.`跟进备注`,  " +
                "      a.`更新时间`,  b.duration,  " +
                "     a.`模式`,  a.`跟进日期`  " +
                "    FROM    `跟进记录表` a  " +
                "    LEFT JOIN sc_call_history b ON a.callId = b.id  " +
                "    WHERE    ISNULL(b.id)  " +
                "    AND a.`跟进日期` =   :date  ) AS a ON b.`电话号码` = a.`电话号码`  " +
                "   AND b.`许可` = a.`许可`  " +
                "   LEFT JOIN `lsh车型` AS c ON c.`车型编号` = b.`车型编号`  " +
                "   LEFT JOIN `用户` AS d ON d.`用户名` = a.`销售顾问`  " +
                "   LEFT JOIN `销售顾问分组` AS e ON e.`销售顾问` = d.`用户名`  " +
                "   LEFT JOIN ( SELECT b.id, b.`许可`, b.`电话号码`, b.`销售顾问`, b.`跟进日期`, b.`跟进结果`, b.`下次跟进时间`, " +
                "  b.`更新时间`, b.`跟进备注`, c.`跟进日期` 上次跟进日期, c.`跟进备注` 上次跟进备注, d.duration 上次通话时长 " +
                " FROM ( SELECT a.id, MAX(b.id) lastId FROM 跟进记录表 a LEFT JOIN 跟进记录表 b ON ( a.`电话号码` = b.`电话号码` AND a.`更新时间` > b.`更新时间` ) " +
                " WHERE a.`跟进日期` = :date GROUP BY a.id ) a JOIN 跟进记录表 b " +
                " ON a.id = b.id LEFT JOIN 跟进记录表 c ON a.lastId = c.id LEFT JOIN sc_call_history d ON c.callId = d.id ) AS j ON (  " +
                "    j.`许可` = a.`许可`  AND j.`电话号码` = a.`电话号码` AND j.id = a.id ) " +
                "LEFT JOIN vw_max_quotedinfo_all g ON b.`顾客编号` = g.顾客编号 " +
                "LEFT JOIN `报价订单表` AS h ON h.`订单编号` = g.`订单编号`  " +
                "LEFT JOIN `报价交车表` AS i ON i.`编号` = g.`交车`  " +
                " WHERE  a.`模式` = '邀约'  " +
                "   AND a.`跟进日期` =   :date  " );
        if (!Strings.isNullOrEmpty(dealer)){
            sqlStr.append(" AND b.`许可`=:permit  ") ;
        }

        sqlStr.append(" UNION ALL  " +
                "    SELECT  " +
                "     d.`许可`,   b.`顾客姓名` '线索姓名',   a.`电话号码` '电话号码',  " +
                "     b.`留档日期` '线索创建日期',  b.`顾客首次渠道` '来源渠道', a.`销售顾问` '销售顾问ID',  " +
                "     a.duration '当天跟进通话时长',   a.`跟进备注` '当天跟进备注',  " +
                "     j.`上次跟进日期` '上一次跟进日期',  j.`上次通话时长` '上一次跟进通话时长',  " +
                "     j.`上次跟进备注` '上一次跟进备注', b.`上次进店日期` '最近一次进店日期',  " +
                "     a.`更新时间` '更新时间', c.`车型` '意向车型', b.`新顾客级别` '意向级别',  " +
                "     d.`人员姓名` '销售顾问', e.`组名` '销售组', " +
                "	h.`订单申请日期` AS '订单日期'," +
                "	DATE_FORMAT(i.`开票时间`, '%Y-%m-%d') AS '开票日期', " +
                "     CURDATE() '跟进日期'  FROM `顾客记录汇总` b  " +
                "    RIGHT JOIN ( SELECT a.id, " +
                "      b.`许可`, a.tel '电话号码', a.sc '销售顾问',  " +
                "      b.`跟进备注`, a.updateTime '更新时间', a.duration,  " +
                "      a.state '模式', a.date '跟进日期'  " +
                "     FROM     sc_call_history a  " +
                "     LEFT JOIN `跟进记录表` b ON a.id = b.callId  " +
                "     AND a.tel = b.`电话号码`  LEFT JOIN `用户` c ON a.sc = c.`用户名` " +
                "     WHERE   ISNULL(b.callId)  " +
                "     AND a.date =   :date  " );
        if(!Strings.isNullOrEmpty(dealer)){
            sqlStr.append( "    AND c.`许可` = :permit ") ;
        }

        sqlStr.append( "    ) AS a ON b.`电话号码` = a.`电话号码`  " +
                "    LEFT JOIN `lsh车型` c ON b.`意向车型A` = c.`车型编号`  " +
                "    LEFT JOIN `用户` AS d ON d.`用户名` = a.`销售顾问`  " +
                "    LEFT JOIN `销售顾问分组` AS e ON e.`销售顾问` = d.`用户名`  " +
                "    LEFT JOIN ( SELECT b.id, b.`许可`, b.`电话号码`, b.`销售顾问`, b.`跟进日期`, b.`跟进结果`, b.`下次跟进时间`, " +
                " b.`更新时间`, b.`跟进备注`, c.`跟进日期` 上次跟进日期, c.`跟进备注` 上次跟进备注, d.duration 上次通话时长 " +
                " FROM ( SELECT a.id, MAX(b.id) lastId FROM 跟进记录表 a LEFT JOIN 跟进记录表 b ON ( a.`电话号码` = b.`电话号码` AND a.`更新时间` > b.`更新时间` ) " +
                " WHERE a.`跟进日期` = :date GROUP BY a.id ) a JOIN 跟进记录表 b ON a.id = b.id LEFT JOIN 跟进记录表 c " +
                " ON a.lastId = c.id LEFT JOIN sc_call_history d ON c.callId = d.id ) AS j ON (   j.`许可` = a.`许可`  " +
                "     AND j.`电话号码` = a.`电话号码`  AND j.id = a.id ) " +
                "LEFT JOIN vw_max_quotedinfo_all g ON b.`顾客编号` = g.顾客编号 " +
                "LEFT JOIN `报价订单表` AS h ON h.`订单编号` = g.`订单编号`  " +
                "LEFT JOIN `报价交车表` AS i ON i.`编号` = g.`交车`   " +
                "    WHERE  a.`模式` = '02'   " +
                "    UNION ALL  " +
                "     SELECT  " +
                "      b.`许可`, b.`顾客姓名` '线索姓名',  " +
                "      a.`电话号码` '电话号码',      b.`线索创建日期` '线索创建日期',  " +
                "      b.`渠道` '来源渠道',     a.`销售顾问` '销售顾问ID',  " +
                "      a.duration '当天跟进通话时长',     a.`跟进备注` '当天跟进备注',  " +
                "      j.`上次跟进日期` '上一次跟进日期',     j.`上次通话时长` '上一次跟进通话时长',  " +
                "      j.`上次跟进备注` '上一次跟进备注',     '' AS '最近一次进店日期',  " +
                "      a.`更新时间` '更新时间',     c.`车型` '意向车型',  " +
                "      b.`客户级别` '意向级别',     d.`人员姓名` '销售顾问',  " +
                "      e.`组名` '销售组'," +
                "	h.`订单申请日期` AS '订单日期'," +
                "	DATE_FORMAT(i.`开票时间`, '%Y-%m-%d') AS '开票日期',   CURDATE() '跟进日期'  " +
                "     FROM   (SELECT * FROM `lsh导入名单` bb WHERE bb.`是否有效线索`='是' AND bb.`是否线索`='是' " );
        if(!Strings.isNullOrEmpty(dealer)){
            sqlStr.append( " AND bb.许可= :permit ") ;
        }

        sqlStr.append("     ) AS b   RIGHT JOIN (  " +
                "      SELECT   a.id, b.`许可`,  " +
                "       a.tel '电话号码',    a.sc '销售顾问',  " +
                "       b.`跟进备注`,    a.updateTime '更新时间',  " +
                "       a.duration,   a.state '模式',  " +
                "       a.date '跟进日期'  FROM  " +
                "       sc_call_history a   LEFT JOIN `跟进记录表` b ON a.id = b.callId " +
                " AND a.tel = b.`电话号码`   LEFT JOIN `用户` c ON a.sc = c.`用户名`  " +
                "      WHERE    ISNULL(b.callId)  " +
                "      AND a.date =   :date " );
        if(!Strings.isNullOrEmpty(dealer)){
            sqlStr.append( "    AND c.`许可` = :permit ") ;
        }

        sqlStr.append( " ) AS a ON b.`电话号码` = a.`电话号码`  " +
                "     LEFT JOIN `lsh车型` AS c ON c.`车型编号` = b.`车型编号`  " +
                "     LEFT JOIN `用户` AS d ON d.`用户名` = a.`销售顾问`  " +
                "     LEFT JOIN `销售顾问分组` AS e ON e.`销售顾问` = d.`用户名`  " +
                "     LEFT JOIN ( SELECT b.id, b.`许可`, b.`电话号码`, b.`销售顾问`, b.`跟进日期`, b.`跟进结果`, b.`下次跟进时间`, b.`更新时间`, b.`跟进备注`, c.`跟进日期` 上次跟进日期," +
                "  c.`跟进备注` 上次跟进备注, d.duration 上次通话时长 FROM ( SELECT a.id, MAX(b.id) lastId FROM 跟进记录表 a LEFT JOIN 跟进记录表 b " +
                " ON ( a.`电话号码` = b.`电话号码` AND a.`更新时间` > b.`更新时间` ) WHERE a.`跟进日期` = :date GROUP BY a.id ) a " +
                " JOIN 跟进记录表 b ON a.id = b.id LEFT JOIN 跟进记录表 c ON a.lastId = c.id LEFT JOIN sc_call_history d ON c.callId = d.id ) AS j ON (  j.`许可` = a.`许可`  " +
                "      AND j.`电话号码` = a.`电话号码`  AND j.id = a.id   )  " +
                "LEFT JOIN vw_max_quotedinfo_all g ON b.`顾客编号` = g.顾客编号 " +
                "LEFT JOIN `报价订单表` AS h ON h.`订单编号` = g.`订单编号`  " +
                "LEFT JOIN `报价交车表` AS i ON i.`编号` = g.`交车`  " +
                "   WHERE a.`模式` = '01' " );
        sqlStr.append( " ) a " );

        if (!Strings.isNullOrEmpty(dealer)) {

            sqlStr.append("   WHERE a.`许可`=:permit ");
        }
        sqlStr.append(" ORDER BY a.`更新时间` DESC ");

        return this.getList(sqlStr.toString(), params);
    }

    @Override
    public PageData getDisplayFlowInfo(String permit, String carType, String startDate, String endDate, String scCode, int pageSize, int pageIndex) {
        Map<String, Object> params = Maps.newHashMap();

        String sql = "  SELECT a.进店编号,\n" +
                " IFNULL(d.`人员姓名`,'') AS 前台,\n" +
                " if(a.`是否首次到店`='是','1','2') AS 是否首次,\n" +
                " if (date_format(a.`到店时间`,'%k')=3 , date_format(a.`到店时间`,'1:%i:%s') , date_format(a.`到店时间`,'%k:%i:%s'))`到店时间`,\n" +
                " if (date_format(a.`离店时间`,'%k')=3 , date_format(a.`离店时间`,'1:%i:%s') , date_format(a.`离店时间`,'%k:%i:%s')) `离店时间`,\n" +
                " a.`停留时间`,\n" +
                " if(a.`是否预约` ='是','1','0') AS 是否预约,\n" +
                " if(a.`是否指定销售顾问` IS NOT NULL,'1','0') 是否指定SC,\n" +
                " zsc.`人员姓名` 指定SC,\n" +
                " jsc.`人员姓名`  接待SC,\n" +
                " if(c.`顾客姓名`='' or c.`顾客姓名` is null,'访客',c.`顾客姓名`) as 顾客姓名,\n" +
                " if(c.`电话号码`='' or c.`电话号码` is null,'NA',c.`电话号码`) as 联系电话,\n" +
                " IF(cx.车型详细 is not null and  cx.车型详细 <>'',cx.车型详细,cx.车型)  意向车型,\n" +
                " a.`保有品牌`  现有品牌,\n" +
                " a.`客户级别`   客户级别,\n" +
                " a.`顾客来源` 首次来源渠道,\n" +
                " a.`信息渠道` 渠道细分备注,\n" +
                " a.`顾客再次来源`   再次来源渠道,\n" +
                " if(a.`是否报价`='是','1','0') as 是否报价,\n" +
                " if(a.`是否订单`='是','1',if(a.`是否订单`='退订','-1','0')) as 是否订单,\n" +
                " if(a.`是否交车`='是','1','0') as 是否交车,\n" +
                " a.`到店目的`   到店目的,\n" +
                " a.`接待备注`  其他目的备注,\n" +
                " a.`进店日期`  日期,\n" +
                " a.`意向车型B`,\n" +
                " a.`意向车型C` " +
                " FROM `顾客进店记录表` AS a \n" +
                " LEFT JOIN `用户` AS d ON a.`操作人` = d.`用户名` and d.角色='前台' \n" +
                "LEFT JOIN `用户` AS zsc ON a.`是否指定销售顾问` = zsc.`用户名` \n" +
                "LEFT JOIN `用户` AS jsc ON a.`销售顾问` = jsc.`用户名` \n" +
                "LEFT JOIN `顾客记录汇总` AS c ON c.`顾客编号` = a.`顾客编号` \n" +
                "LEFT JOIN lsh车型 as cx   on  cx.车型编号= a.`意向车型A`" +
                " WHERE a.许可 = :permit ";
        params.put("permit", permit);

        if (StringUtils.isNotNullOrBlank(scCode)) {
            sql += " and a.销售顾问 = :scCode ";
            params.put("scCode", scCode);
        }
        if (startDate != null && endDate != null) {
            sql += " and a.`进店日期` >= date_format(:startDate,'%Y-%m-%d') AND a.`进店日期` <= date_format(:endDate,'%Y-%m-%d')";
            params.put("startDate", startDate);
            params.put("endDate", endDate);
        }

        if (StringUtils.isNotNullOrBlank(carType)) {
            sql += " and cx.车型 like :carType ";
            params.put("carType", "%" + carType + "%");
        }

        PageData pd = this.getPage(sql,params, pageSize, pageIndex);
        List  list = pd.getData();
        if (list.size() > 0) {
            for (int i = 0; i < list.size(); i++) {
                Map m = (Map) list.get(i);
                Integer id = (Integer) (m.get("进店编号"));
                //查询到试驾车和车型表
                String carDetails = "";
                List<顾客试驾记录Model> modelList = repository顾客试驾记录.findBy进店编号(id);
                String startTime = "";//试驾开始时间
                String endTime = "";//试驾结束时间
                String totalTime = "";//试驾时长
                try {
                    if(modelList.size()==0){//若试驾记录表查不到试驾记录则取查临时接待记录
                        临时接待记录Model 临时接待记录Model = teampRecepRepository.getModelByEnterNums(id);
                        if(临时接待记录Model!=null){
                            startTime += 临时接待记录Model.get开始试驾时间() == null ? "" : String.valueOf(临时接待记录Model.get开始试驾时间());
                            endTime +=  临时接待记录Model.get结束试驾时间() ==null ? "" : String.valueOf(临时接待记录Model.get结束试驾时间());
                            if(临时接待记录Model.get开始试驾时间() !=null&&临时接待记录Model.get结束试驾时间()!=null ){
                                totalTime += DateFormatUtils.betweenMinutes(临时接待记录Model.get开始试驾时间(),临时接待记录Model.get结束试驾时间());
                                carDetails+=m.get("意向车型");}
                        }
                    }

                }
                catch (Exception e){
                    logger.error("当日线索跟进试驾时间获取失败。进店编号={}",id,e);
                }
                for (int j = 0; j < modelList.size(); j++) {
                    if (j > 0) {
                        startTime = startTime + ";";
                        endTime = endTime + ";";
                        totalTime = totalTime + ";";
                        carDetails = carDetails + ";";
                    }
                    顾客试驾记录Model model试驾记录 = modelList.get(j);
                    Integer carId = model试驾记录.get试驾车型编号();
                    Lsh车型Model lsh车型Model = lsh车型Repository.findby车型编号(carId);
                    if (lsh车型Model != null)
                        carDetails = carDetails + lsh车型Model.get车型详细();
                    startTime += model试驾记录.get试驾开始时间() == null ? "" : String.valueOf(model试驾记录.get试驾开始时间());
                    endTime +=  model试驾记录.get试驾结束时间() ==null ? "" : String.valueOf(model试驾记录.get试驾结束时间());
                    totalTime += model试驾记录.get试驾时长() == null ? "" : String.valueOf(model试驾记录.get试驾时长());
                }
                if (carDetails==null||carDetails.equals("null")){
                    carDetails="";
                }
                m.put("试驾车型", carDetails);
                m.put("试驾开始", startTime);
                m.put("试驾结束", endTime);
                m.put("试驾时长", totalTime);
            }
        }

        return pd;
    }

    @Override
    public List<String> getDisplayFlowInfo(String permit, String startDate,
                                           String endDate) {
        PageData pd = this.getDisplayFlowInfo(permit, null, startDate, endDate, null, 1000 ,0 );

        return pd.getData();
    }

    @Override
    public Map<String, Object> getClueInfo(String permit) {
        Map<String, Object> params = Maps.newHashMap();
        params.put("permit", permit);
        Map <String, Object>  m=new HashMap();
        m.put("recommend",0);
        m.put("buyAgain",0);
        m.put("network",0);
        m.put("initiative",0);
        m.put("callin",0);
        m.put("active",0);
        m.put("sleep",0);
        String sql="select\n" +
                "SUM(IF(a.渠道= '推荐',1,0)) as  recommend,\n" +
                "SUM(IF(a.渠道= '再购',1,0)) as  buyAgain,\n" +
                "SUM(IF(a.渠道= '网络',1,0)) as  network,\n" +
                "SUM(IF(a.渠道= '主动集客',1,0)) as  initiative,\n" +
                "SUM(IF(a.渠道= '呼入',1,0)) as  callin,\n" +
                "d.active,\n" +
                "d.sleep\n" +
                "FROM\n" +
                "lsh导入名单 as a\n" +
                "JOIN `lsh车型` as b on a.`车型编号`=b.车型编号\n" +
                " LEFT JOIN (\n" +
                "select\n" +
                "a.`许可`,\n" +
                "SUM(IF(a.渠道= '活跃',a.有效线索数,0)) as  active,\n" +
                "SUM(IF(a.渠道= '休眠',a.有效线索数,0)) as  sleep\n" +
                "from   线索统计  as a\n" +
                "JOIN `lsh车型` as b on a.`车型编号`=b.车型编号\n" +
                "where  a.许可=:permit \n" +
                "AND   a.周期= DATE_FORMAT(CURDATE(),'%Y%m') and a.车型编号  <> 0\n" +
                ")  d  on  d.许可=a.许可\n" +
                "where    a.许可=:permit \n" +
                "AND (客户级别 is not null and 客户级别 <> '' and 客户级别 <> 'E:六个月以上订单')\n" +
                "AND  DATE_FORMAT(a.`线索创建日期` ,'%Y-%m') = DATE_FORMAT( CURDATE(),'%Y-%m')\n" +
                "AND a.车型编号    <> 0\n" +
                "      ";
        List  list=this.getList(sql,params);

        if (list!=null&&list.size() > 0) {
            m = (Map) list.get(0);
        }


        return m;
    }

    @Override
    public List getBoutique(String dealer,String quoId) {
        Map<String, Object> params = Maps.newHashMap();
        Map<String, Object>  mp    = new HashMap();
        报价单表Model mode = 报价单表repository.findBy报价编号(Integer.parseInt(quoId));
        params.put("quoId",mode.get精品());
        String sql = "SELECT\n" +
                "  IF(a.origin_type=1,a.origin_shop_id,(SELECT aa.产品编号 FROM lsh套餐信息 aa WHERE aa.套餐编号 = a.id))\t 精品编号, " +
                "\ta.名称 精品名称,\n" +
                "\ta.数量 数量,\n" +
                "\t'个' AS \"单位\",\n" +
                "\tROUND(a.折后价, 2) 单价,\n" +
                "\tifnull(a.数量 * a.折后价,0) 金额,   ifnull(a.数量 * a.原价,0) 精品总金额, " +
                "  \"\" 工时,\n" +
                "\"\" 领料人员,\n" +
                "\"\" 施工人员,\n" +
                "\"\" 质检,\n" +
                "\"\" 完检,\n" +
                "\ta.备注,\n" +
                "b.初始报价单号 合同号,\n" +
                "g.人员姓名 销售顾问,\n" +
                "d.开票客户姓名 客户名称,\n" +
                "c.名称 联系人,\n" +
                "c.详细地址 联系地址,\n" +
                "c.电话 联系电话,\n" +
                "e.车型,\n" +
                "f.车架号,\n" +
                "f.发动机号,\n" +
                "f.颜色 外观\n" +
                "FROM\n" +
                "\t报价精品详细表 a\n" +
                "JOIN 报价单表 b ON b.精品 = a.编号\n" +
                "LEFT JOIN 报价客户表 c ON b.客户详细=c.`编号`\n" +
                "LEFT JOIN 报价交车表 d ON b.交车=d.编号\n" +
                "LEFT JOIN lsh车型 e ON b.车型编号=e.车型编号\n" +
                "LEFT JOIN lsh库存表 f ON b.库存编号=f.库存编号\n" +
                "left join 用户 g on b.销售顾问= g.用户名\n" +
                "\n" +
                "WHERE a.编号= :quoId ";

        return this.getList(sql,params);

    }
}
